package com.pointi.emoTools ;

import java.sql.Connection ;
import java.sql.DatabaseMetaData ;
import java.sql.DriverManager ;
import java.sql.ResultSet ;
import java.sql.SQLException ;
import java.sql.Statement ;
import java.text.ParseException ;
import java.text.SimpleDateFormat ;
import java.util.Date ;

import com.pointi.emoTools.DefineParam ;

public class emoJDBC
{
	// ____________________________________________________________________________________________________
	// JDBC | DEFINE

	static public final int DB_TYPE_UNKNOWN = 0 ;
	static public final int DB_TYPE_ORACLE = 1 ;
	static public final int DB_TYPE_MYSQL = 2 ;
	static public final int DB_TYPE_MSSQL = 3 ;

	// ____________________________________________________________________________________________________
	// JDBC | Connection

	protected DefineParam define ;
	protected emoLicense conn_data ;
	protected int n_database_type = DB_TYPE_MYSQL ; // TODO for DEMO
	protected Connection mConnection = null ;

	public emoJDBC() { define = new DefineParam() ; }

	public void SetDatabaseParam(emoLicense db_param) {
		conn_data = db_param ;
		/**/ if (conn_data.s_database_type.toLowerCase().equals("oracle")) n_database_type = DB_TYPE_ORACLE ;
		else if (conn_data.s_database_type.toLowerCase().equals("mysql")) n_database_type = DB_TYPE_MYSQL ;
		else if (conn_data.s_database_type.toLowerCase().equals("mssql")) n_database_type = DB_TYPE_MSSQL ;
	}
	public int fnConnectDB()	{
		int nReturn = define.DB_CONNECT_FAIL ;
		/**/ if (n_database_type == DB_TYPE_ORACLE) nReturn = fnConnectDB_oracle() ;
		else if (n_database_type == DB_TYPE_MYSQL) nReturn = fnConnectDB_mysql() ;
		else if (n_database_type == DB_TYPE_MSSQL) nReturn = fnConnectDB_mssql() ;
		return nReturn ;
	}
	public int fnDisconnectDB() {
		int nReturn = define.DB_CLOSE_FAIL ;
		try { 
			if (mConnection != null) {
				mConnection.close() ;
				if (mConnection.isClosed())
					nReturn = define.DB_CLOSE_SUCCESS ;
			}
		}
		catch(Exception ignored) { nReturn = define.DB_CLOSE_FAIL ; }
		return nReturn ;
	}
	public int fnIsConnectedDB() {
		int nReturn = define.DB_CONNECT_NOW ;
		try { 
			if (mConnection != null) {
				if (mConnection.isClosed())
					nReturn = define.DB_CONNECT_LOST ;
			}
		}
		catch(Exception ignored) { nReturn = define.DB_CLOSE_FAIL ; }
		return nReturn ;
	}
	public int fnReconnectDB() {
		int nReturn = define.S_OK ;
		nReturn = fnIsConnectedDB() ;
		if (nReturn == define.DB_CONNECT_LOST) {
			while (true) {
				nReturn = fnConnectDB() ;
				if (nReturn == define.DB_CONNECT_SUCCESS) break ;
				System.out.println("DB Connect - "+nReturn) ;
			}
		}
		return nReturn ;
	}
	private int fnConnectDB_oracle() {
		int nReturn = define.DB_CONNECT_FAIL ;
		try	{
			Class.forName("oracle.jdbc.driver.OracleDriver") ;
			nReturn = define.DB_LOAD_SUCCESS ;
		}
		catch(ClassNotFoundException e) { nReturn = define.DB_LOAD_FAIL ; }
		String url = "jdbc:oracle:thin:@"+conn_data.s_database_address+":"+conn_data.s_database_port+":"+conn_data.s_database_name ;
		try {
			mConnection = DriverManager.getConnection(url, conn_data.s_database_accountid, conn_data.s_database_accountpw) ;
			if (mConnection != null)
				nReturn = define.DB_CONNECT_SUCCESS ;
		} catch(SQLException e) { nReturn = define.DB_CONNECT_FAIL ; }
		return nReturn ;
	}
	private int fnConnectDB_mysql() {
		int nReturn = define.DB_CONNECT_FAIL ;
		try {
			Class.forName("com.mysql.jdbc.Driver") ;
			nReturn = define.DB_LOAD_SUCCESS ;
		} catch(ClassNotFoundException e) { nReturn = define.DB_LOAD_FAIL ; }
		String url = "jdbc:mysql://"+conn_data.s_database_address+":"+conn_data.s_database_port+"/"+conn_data.s_database_name ;
		try {
			mConnection = DriverManager.getConnection(url, conn_data.s_database_accountid, conn_data.s_database_accountpw) ;
			if (mConnection != null)
				nReturn = define.DB_CONNECT_SUCCESS ;
		} catch(SQLException e) { nReturn = define.DB_CONNECT_FAIL ; }
		return nReturn ;
	}
	private int fnConnectDB_mssql() {
		int nReturn = define.DB_CONNECT_FAIL ;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver") ;
			nReturn = define.DB_LOAD_SUCCESS ;
		} catch(ClassNotFoundException e) { nReturn = define.DB_LOAD_FAIL ; }
		String url = "jdbc:sqlserver://"+conn_data.s_database_address+":"+conn_data.s_database_port+" ;databaseName="+conn_data.s_database_name ;
		try {
			mConnection = DriverManager.getConnection(url, conn_data.s_database_accountid, conn_data.s_database_accountpw) ;
			if (mConnection != null)
				nReturn = define.DB_CONNECT_SUCCESS ;
		} catch(SQLException e) { nReturn = define.DB_CONNECT_FAIL ; }
		return nReturn ;
	}

	// ____________________________________________________________________________________________________
	// JDBC | Date, Calendar

	public Date fnGetStringDateToDate(String s_date) {
		Date d_return = new Date() ;
		try {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd") ;
			d_return = format.parse(s_date) ;
		} catch (ParseException ex) { ex.printStackTrace() ; }		
		return d_return ;
	}
	public String fnConvertDateTime(String inDateTime, int inOption) {
		SimpleDateFormat sdf = null ;
		switch (inOption) {
		case 1 : // Get Date Only
			sdf = new SimpleDateFormat("yyyy-MM-dd") ;
			// return sdf.format(this.fnGetStringDateToDate(inDateTime)) ; // for ORIGINAL
			return sdf.format(new Date()) ; // TODO for DEMO
		case 2 : // Get Time Only
			sdf = new SimpleDateFormat("HH:mm:ss") ;
			return sdf.format(this.fnGetStringDateToDate(inDateTime)) ;
		default : // Get Date Time
			return inDateTime ;
		}
	}
	public String fnGetYYYYMM() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM") ;
		return sdf.format(new Date()) ;
	}
	public String fnGetYYYYMMDD() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd") ;
		return sdf.format(new Date()) ;
	}
	public String fnGetYYYYMM(String inDate) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM") ;
		return sdf.format(this.fnGetDateFromYYYY_MM_DD(inDate)) ;
	}
	public String fnGetYYYYMMDD(String inDate) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd") ;
		return sdf.format(this.fnGetDateFromYYYY_MM_DD(inDate)) ;
	}
	public Date fnGetDateFromYYYY_MM_DD(String inDate) {
		Date outDate = null ;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd") ;
		try { outDate = sdf.parse(inDate) ; }
		catch (Exception e) { e.printStackTrace() ; }
		return outDate ;
	}
	public String fnGetRegDate() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
		return sdf.format(new Date()) ;
	}
	public String fnGetStartTime() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
		return sdf.format(new Date()) ;
	}
	public String fnGetEndTime(String dur)
	{
		int sec = Integer.valueOf(dur) ;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
		return sdf.format(new Date(System.currentTimeMillis() + (sec * 1000))) ;
	}

	// ____________________________________________________________________________________________________
	// JDBC | Common

	public boolean isTableExist(String inTableName) {
		try {
			DatabaseMetaData meta = mConnection.getMetaData() ;
			ResultSet res = meta.getTables(null, null, inTableName, null) ;
			res.next() ;
			if (res.getRow() == 1) return true ;
		} catch (Exception e) { e.printStackTrace() ; }
		return false ;
	}
	public void fnExecuteInsertOrUpdateQuery(String inQuery) {
		try {
			Statement mStatement = mConnection.createStatement() ;
			mStatement.executeUpdate(inQuery) ;
		} catch(SQLException se) { 
			System.out.println(inQuery) ;
			System.out.println(se.getMessage()) ;
		}
	}
	public ResultSet fnGetResultSet(String inQuery)	{
		try {
			Statement mStatement = mConnection.createStatement() ;
			ResultSet outRS = mStatement.executeQuery(inQuery) ;
			return outRS ;
		} catch(SQLException se) {
			System.out.println(inQuery) ;
			System.out.println(se.getMessage()) ;
		}
		return null ;
	}
	public void sleep(int inTIME) {
		try {
			Thread.sleep(inTIME) ;
		} catch (Exception e) { e.printStackTrace() ; }
	}

	// ____________________________________________________________________________________________________
	// SYSTEM_PARAMETER

	// ____________________________________________________________________________________________________
	// t_sysparam_operate_log

	public int fnGetOperationSeq()
	{
		Statement mST = null ;
		ResultSet mRS = null ;
		int nReturn = 0 ;
		SimpleDateFormat new_date = new SimpleDateFormat("yyyy-MM-dd") ;
		String s_check_new_date = new_date.format(new Date()) ;
		try {
			mST = mConnection.createStatement() ;
			String query_get_apply_date =
					" SELECT apply_date, seq " +
							" FROM t_sysparam_operate_log " +
							" ORDER BY apply_date DESC ;" ;
			mRS = mST.executeQuery(query_get_apply_date) ;
			Date m_new_date = new Date() ;
			m_new_date = fnGetStringDateToDate(s_check_new_date) ;
			Date m_old_date = new Date() ;
			Date m_rs_date = new Date() ;
			while (mRS.next()) {
				m_rs_date = fnGetStringDateToDate(mRS.getString("apply_date")) ;
				/**/ if (m_new_date.after(m_rs_date)) { nReturn = mRS.getInt("seq") ; break ; }
				else if (m_new_date.after(m_rs_date) && m_new_date.before(m_old_date)) { nReturn = mRS.getInt("seq") ; break ; }
				else if (m_new_date.equals(m_rs_date)) { nReturn = mRS.getInt("seq") ; break ; }
				else { nReturn = mRS.getInt("seq"); }
				m_old_date = m_rs_date ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return nReturn ;
	}

	// ____________________________________________________________________________________________________
	// t_sysparam_df_guideline

	public void fnGetSysparamGuideLineData(String inDate, SysGuidelineData inGuidelineData)
	{
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT * " +
				" FROM	t_sysparam_df_guideline AS A, " +
				" (SELECT TOP 1 seq, apply_date " +
				" FROM t_sysparam_operate_log " +
				" WHERE apply_date <= '" + inDate + "' " +
				" ORDER BY apply_date DESC) AS B " +
				" WHERE A.oplog_seq = B.seq " ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				inGuidelineData.fnSetParameter(mRS) ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	// ____________________________________________________________________________________________________
	// t_sysparam_df_exception

	public int fnGetSysParamExceptionCount_ai(int n_operation_seq) 
	{
		int nResult = 0 ;
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		try {
			query = " SELECT COUNT(*) AS cnt " +
					" FROM t_sysparam_df_exception " +
					" WHERE oplog_seq = " + n_operation_seq + " AND exception_type = 0 ; " ;
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while(mRS.next()) {
				nResult = mRS.getInt("cnt");
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return nResult ;
	}

	// ____________________________________________________________________________________________________
	// System Parameter // Engine Configuration // t_sysparam_engine_config

	public void fnGetSysParamEngineConfig(emoConfig eCONF)
	{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd") ; 
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT TOP 1 * " +
				" FROM t_sysparam_engine_config AS eCONF " +
				" WHERE eCONF.apply_date <= '" + sdf.format(new Date()) + "' " + 
				" ORDER BY eCONF.apply_date DESC ; " ;
		try
		{
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			if (mRS != null)
			{
				while(mRS.next())
				{
					eCONF.CF_L2_THD(mRS.getInt("CF_L2_THD")) ;
					eCONF.CF_L3_TARGET_CNT(mRS.getInt("CF_L3_TARGET_CNT")) ;
					eCONF.CF_L3_NEIGHBOR_CNT(mRS.getInt("CF_L3_NEIGHBOR_CNT")) ;
					eCONF.SF_TARGET_CNT(mRS.getInt("SF_TARGET_CNT")) ;
					eCONF.VE_LIMIT(mRS.getInt("VE_LIMIT")) ;
					eCONF.VE_CNT(mRS.getInt("VE_CNT")) ;
					eCONF.VE_HZ(mRS.getInt("VE_HZ")) ;
					eCONF.FILTER_STRENGTH(mRS.getInt("FILTER_STRENGTH")) ;
					eCONF.AI_MIN(mRS.getDouble("AI_MIN")) ;
					eCONF.AI_MAX(mRS.getDouble("AI_MAX")) ;
					eCONF.HT_CNT(mRS.getInt("HT_CNT")) ;
					eCONF.HT_TYPE(mRS.getInt("HT_TYPE")) ;
				}
			}
		}
		catch(SQLException se) { se.printStackTrace() ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
}
